<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
class activity_model  extends CI_Model {

    public function __construct() {
        error_reporting(0);
        parent::__construct();
    }
    
    public function get_photos($act_id){
        return $this->db->select("*")->from("activity_photos")->where("ref_act_id",$act_id)->order_by("activity_photos_id","ASC")->get();
    }
    
    public function get_activity_groups_relation_by_activity_id($act_id){
        return $this->db->select("*")->from("activity_groups_relation")->where("ref_act_id",$act_id)->limit(1)->get();
    }

    /* แสดงข้อมูลรายงานกิจกรรมที่นักศึกษาเข้าร่วม */
    public function search_activity_event_with_sum( $input ){
       
        $start = ($_POST["page-limit"]=="")?0:$_POST["page-limit"];
                    $this->db->select(" SQL_CALC_FOUND_ROWS *,
                        count( CASE when  collegians_of_activity.is_active = 'Y'   then   collegians_of_activity.ref_collegian_id  END) as count_person ,
                        count( CASE when  collegians_of_activity.is_active = 'Y'  AND collegians_of_activity.allow_activity = 'Y'   then   collegians_of_activity.ref_collegian_id  END) as count_signin_activity ,
                        count( CASE when  collegians_of_activity.is_active = 'Y'  AND collegians_of_activity.allow_activity ='N'   then   collegians_of_activity.ref_collegian_id  END) as no_signin ,
                        count( CASE when  collegians_of_activity.is_active = 'Y'  AND collegians_of_activity.allow_activity ='N'   then   collegians_of_activity.ref_collegian_id  END) as register_but_signin
                       
                        ",false)->from("activity_events as act_event")
                            ->join("activity_groups_relation","activity_groups_relation.ref_act_id = act_event.act_id","left")
                            ->join("group_major_relation","group_major_relation.ref_activity_groups_relation_id = activity_groups_relation.activity_groups_relation_id","left")
                            ->join("collegians_of_activity" , "collegians_of_activity.ref_group_major_relation_id = group_major_relation.group_major_relation_id","left");
        if($input["month_session"] != ""){
            
            switch (trim($input["month_session"])){
                case "T1":
                    $start_date = "6";
                    $end_date = "9";
                    $start_year1 = $input[year];
                    $start_year2 = $input[year];
                    break;
                case "T2":
                    $start_date = "10";
                    $end_date = "3";
                    $start_year1 = $input[year];
                    $start_year2 = ($input[year]+1);
                    break;
                case "T3":
                    $start_date = "3";
                    $end_date = "6";
                    $start_year1 = $input[year];
                    $start_year2 = $input[year];
                    break;
                default :
                    $start_date = "1";
                    $end_date = "12";
                    $start_year1 = $input[year];
                    $start_year2 = $input[year];
            }
            
            $this->db->where("( MONTH(act_event.act_start_date)  BETWEEN  {$start_date}  AND  {$end_date}   )",null,false);
            $this->db->where("( YEAR(act_event.act_start_date)  BETWEEN  {$start_year1}  AND   {$start_year2}   )",null,false);
        }

        $this->db->where("( act_event.act_title   LIKE   '%$input[txt_search]%'  )",null,false);
        $this->db->group_by("act_event.act_id");
        $this->db->order_by("act_id","DESC");
        $this->db->limit(15,$_POST["page-limit"]);
        return $this->db->get();
    }
    
    public function search_activity_event($input){
        $start = ($_POST["page-limit"]=="")?0:$_POST["page-limit"];
        $this->db->select("SQL_CALC_FOUND_ROWS * ,
            (CASE  WHEN NOW()  > ADDDATE(act_end_date,1)    THEN  'ปิดกิจกรรม'  
                        WHEN NOW() <= ADDDATE(act_end_date,1)   THEN  'ดำเนินกิจกรรม'  
            END)
            as  dateout",false)->from("activity_events");
        if($input["month_session"] != ""){
            
            switch (trim($input["month_session"])){
                case "T1":
                    $start_date = "6";
                    $end_date = "9";
                    $start_year1 = $input[year];
                    $start_year2 = $input[year];
                    break;
                case "T2":
                    $start_date = "10";
                    $end_date = "3";
                    $start_year1 = $input[year];
                    $start_year2 = ($input[year]+1);
                    break;
                case "T3":
                    $start_date = "3";
                    $end_date = "6";
                    $start_year1 = $input[year];
                    $start_year2 = $input[year];
                    break;
                default :
                    $start_date = "1";
                    $end_date = "12";
                    $start_year1 = $input[year];
                    $start_year2 = $input[year];
            }
            
            $this->db->where("( MONTH(activity_events.act_start_date)  BETWEEN  {$start_date}  AND  {$end_date}   )",null,false);
            $this->db->where("( YEAR(activity_events.act_start_date)  BETWEEN  {$start_year1}  AND   {$start_year2}   )",null,false);
        }

        $this->db->where("( activity_events.act_title   LIKE   '%$input[txt_search]%'  )",null,false);
        $this->db->order_by("act_id","DESC");
        $this->db->limit(15,$start);
        return $this->db->get();
    }
    
    public function get_activity($activity_id){
        $this->db->select("*,IF(NOW() >= act_start_reg_date AND NOW() <= act_end_reg_date,'Y','N') as is_allow ",false)->from("activity_events");
        $this->db->where("act_id",  mysql_escape_string($activity_id));
        $this->db->limit(1);
        return $this->db->get()->row();
    }
    
    public function update_activity($input){
        $sql = "UPDATE activity_events SET  
                            act_title = '$input[txt_activity_title]',
                            act_description = '$input[txt_description]' , 
                            act_start_reg_date  = '$input[txt_activity_start_date]' , 
                            act_end_reg_date = '$input[txt_activity_end_date]' , 
                            act_start_date = '$input[txt_activity_start_reg_date]' , 
                            act_end_date  = '$input[txt_activity_end_reg_date]' , 
                            act_location  = '$input[txt_activity_location]'   ,
                            score_max = '$input[txt_activity_score]'
                   WHERE  act_id = '$input[act_id]' ";
        $this->db->query($sql);
    }
    
    public function add_activity($input){
        $sql = "INSERT  INTO activity_events(
                      act_id ,
                      act_title ,
                      act_description , 
                      act_date_create , 
                      act_start_reg_date , 
                      act_end_reg_date , 
                      act_start_date , 
                      act_end_date , 
                      ref_group_teacher_relation_id , 
                      status , 
                      act_type , 
                      act_location ,
                      score_max
        )VALUES(
                      NULL , 
                      '$input[txt_activity_title]' , 
                      '$input[txt_description]' , 
                       NOW() , 
                      '$input[txt_activity_start_date]' , 
                      '$input[txt_activity_end_date]' , 
                      '$input[txt_activity_start_reg_date]' , 
                      '$input[txt_activity_end_reg_date]' , 
                      '0' ,  
                      'OPEN',
                     '$input[activity_type]' , 
                     '$input[txt_activity_location]' , 
                     '$input[txt_activity_score]'
        )";
        $is_active = "Y";
        if($input["activity_type"] == "NO"){
            $is_active = "N";
        }
            
        $this->db->query($sql);
        $last_activity_id = $this->db->insert_id();
        $sql_activity_groups_relation = "INSERT  INTO activity_groups_relation VALUES(
            NULL , '{$last_activity_id}'
        )";
        $this->db->query($sql_activity_groups_relation);
        $last_activity_groups_relation_id = $this->db->insert_id();
        if($this->db->affected_rows() > 0){
            
                        foreach($input["teacher_data_ch"] as $teacher_id){
                            $sql = "INSERT  INTO group_teacher_relation VALUES(
                                          NULL , 
                                          '{$last_activity_groups_relation_id}'  ,
                                          '{$teacher_id}'
                            )";
                            $this->db->query($sql);
                        }
                        /* กรณีเป็นกิจกรรมเลือกไม่ต้องเพิ่มข้อมูลนักศึกษาแต่ละสาขาเข้าไปก่อนหน้า */
                        $year_class = 4;
                       if(strtoupper(trim($input["activity_type"])) != "NO"){
                               
                                foreach($input["major_data_ch"] as $index => $major_id){
                                                $year_class = $_POST[year_class][$index];
                                                $year_class_array = explode(",",$year_class);
                                                foreach($year_class_array as $v){
                                                        $sql = "INSERT  INTO group_major_relation VALUES(
                                                                      NULL , 
                                                                      '{$last_activity_groups_relation_id}'  ,
                                                                      '{$major_id}' ,
                                                                      '{$v}'
                                                        )";
                                                        $this->db->query($sql);
                                                        $last_group_major_relation_id = $this->db->insert_id();
                                                        if($this->db->affected_rows() > 0){
                                                               $this->add_collegians_of_activity($v, $major_id , $last_group_major_relation_id , $is_active);
                                                        }
                                                }

                                }
                        }
         }
            
        
    }
    
    
    /* บันทึกข้อมูลนักศึกษาที่เข้าร่วมกิจกรรม */
    public function add_collegians_of_activity($year_class ,  $major_id , $major_groups_relation_id , $is_active = "YES"){
        if($major_id != ""){
            $this->load->model("collegian_model");
            $collegian_datay = $this->db->select("*")->from("collegian")->join("majors","majors.major_id = collegian.ref_major_id","inner")
                                                 ->where("collegian.ref_major_id",$major_id)->get();
            foreach($collegian_datay->result_array() as $row){
                if(get_level($row["collegian_code"])  ==  $year_class){
                        $is_code = md5($row["collegian_date"] . "-" . $row["collegian_id"] . rand(0, 5000));
                        $sql  = "INSERT   INTO collegians_of_activity (
                                         collegians_of_activity_id , 
                                         ref_group_major_relation_id ,
                                         ref_collegian_id , 
                                         code_active , 
                                         is_active , 
                                         allow_activity , 
                                         date_code_active 
                                     )VALUES(
                                         NULL , 
                                         '{$major_groups_relation_id}' ,
                                         '{$row[collegian_id]}'  , 
                                         '{$is_code}' ,
                                         '{$is_active}' ,
                                         'N' ,
                                         NULL
                         )";
                         $this->db->query($sql);
                         file_put_contents("C:/asp.txt", $sql);
                }
            }
        }
        return $this;
    }
    
    public function delete_major_of_activity($major_relation_id){
            $sql = "DELETE FROM group_major_relation WHERE group_major_relation_id = '{$major_relation_id}' ";
            $this->db->query($sql);
             if($this->db->affected_rows() > 0){
                     $sql = "DELETE  FROM collegians_of_activity WHERE  ref_group_major_relation_id = '{$major_relation_id}' ";
                     $this->db->query($sql);
                     echo $this->db->affected_rows();
             }
             return $this;
    }

}
?>
